#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
销售统计服务

基于 trade_orders, trade_order_goods, goods, goods_specs, user 表
提供全面的销售数据统计和分析功能
"""

from typing import Dict, Any, List, Optional
from datetime import datetime, timedelta
import json
from app.sql.mysql_main import main_opera as query_db
import logging
from app.repositories.sales_repository import SalesRepository

logger = logging.getLogger(__name__)

class SalesStatisticsService:
    """销售统计服务类"""
    
    def __init__(self):
        self.sales_repository = SalesRepository()
    
    def get_daily_sales(self, date: Optional[str] = None) -> Dict[str, Any]:
        """获取当日销量统计"""
        try:
            # 如果没有指定日期，使用今天
            if not date:
                date = datetime.now().strftime('%Y-%m-%d')
            
            # 当日订单统计
            order_sql = """
                SELECT 
                    COUNT(*) as order_count,
                    COUNT(DISTINCT buyer_nick) as customer_count,
                    SUM(CAST(receivable AS DECIMAL(10,2))) as total_amount,
                    SUM(CAST(goods_amount AS DECIMAL(10,2))) as goods_amount,
                    SUM(CAST(post_amount AS DECIMAL(10,2))) as shipping_amount,
                    AVG(CAST(receivable AS DECIMAL(10,2))) as avg_order_amount
                FROM trade_orders 
                WHERE DATE(created) = %s
            """
            
            order_result = query_db(order_sql, [date], True)
            order_stats = order_result[0] if order_result else {}
            
            # 当日商品销量统计
            goods_sql = """
                SELECT 
                    COUNT(*) as goods_item_count,
                    SUM(CAST(num AS UNSIGNED)) as total_quantity,
                    COUNT(DISTINCT goods_id) as unique_goods_count
                FROM trade_order_goods 
                WHERE DATE(created) = %s
            """
            
            goods_result = query_db(goods_sql, [date], True)
            goods_stats = goods_result[0] if goods_result else {}
            
            # 当日热销商品Top5
            top_goods_sql = """
                SELECT 
                    tog.goods_id,
                    tog.goods_name,
                    SUM(CAST(tog.num AS UNSIGNED)) as total_sold,
                    SUM(CAST(tog.total_amount AS DECIMAL(10,2))) as total_sales_amount,
                    COUNT(*) as order_count,
                    AVG(CAST(tog.price AS DECIMAL(10,2))) as avg_price,
                    COALESCE(g.brand_name, '') as brand_name,
                    COALESCE(g.class_name, '') as category_name
                FROM trade_order_goods tog
                LEFT JOIN goods g ON tog.goods_id = g.goods_id
                WHERE DATE(tog.created) = %s
                GROUP BY tog.goods_id, tog.goods_name
                ORDER BY total_sold DESC
                LIMIT 5
            """
            
            top_goods_result = query_db(top_goods_sql, [date], True) or []
            
            # 当日销售时段分布
            hourly_sql = """
                SELECT 
                    HOUR(created) as hour,
                    COUNT(*) as order_count,
                    SUM(CAST(receivable AS DECIMAL(10,2))) as hour_amount
                FROM trade_orders 
                WHERE DATE(created) = %s
                GROUP BY HOUR(created)
                ORDER BY hour
            """
            
            hourly_result = query_db(hourly_sql, [date], True) or []
            
            return {
                "success": True,
                "message": "获取当日销量统计成功",
                "data": {
                    "date": date,
                    "summary": {
                        "order_count": int(order_stats.get('order_count', 0)),
                        "customer_count": int(order_stats.get('customer_count', 0)),
                        "total_amount": float(order_stats.get('total_amount', 0)),
                        "goods_amount": float(order_stats.get('goods_amount', 0)),
                        "shipping_amount": float(order_stats.get('shipping_amount', 0)),
                        "avg_order_amount": float(order_stats.get('avg_order_amount', 0)),
                        "goods_item_count": int(goods_stats.get('goods_item_count', 0)),
                        "total_quantity": int(goods_stats.get('total_quantity', 0)),
                        "unique_goods_count": int(goods_stats.get('unique_goods_count', 0))
                    },
                    "top_goods": [
                        {
                            "goods_id": item.get('goods_id'),
                            "goods_name": item.get('goods_name'),
                            "total_sold": int(item.get('total_sold', 0)),
                            "total_sales_amount": float(item.get('total_sales_amount', 0)),
                            "order_count": int(item.get('order_count', 0)),
                            "avg_price": float(item.get('avg_price', 0)),
                            "brand_name": item.get('brand_name', ''),
                            "category_name": item.get('category_name', '')
                        }
                        for item in top_goods_result
                    ],
                    "hourly_distribution": [
                        {
                            "hour": int(item.get('hour', 0)),
                            "order_count": int(item.get('order_count', 0)),
                            "amount": float(item.get('hour_amount', 0))
                        }
                        for item in hourly_result
                    ]
                }
            }
            
        except Exception as e:
            return {"success": False, "message": f"获取当日销量统计失败：{str(e)}"}
    
    def get_weekly_sales(self, start_date: Optional[str] = None) -> Dict[str, Any]:
        """获取一周销量统计"""
        try:
            # 如果没有指定开始日期，使用本周一
            if not start_date:
                today = datetime.now()
                days_since_monday = today.weekday()
                start_date = (today - timedelta(days=days_since_monday)).strftime('%Y-%m-%d')
            
            end_date = (datetime.strptime(start_date, '%Y-%m-%d') + timedelta(days=6)).strftime('%Y-%m-%d')
            
            # 一周订单统计
            weekly_summary_sql = """
                SELECT 
                    COUNT(*) as order_count,
                    COUNT(DISTINCT buyer_nick) as customer_count,
                    SUM(CAST(receivable AS DECIMAL(10,2))) as total_amount,
                    SUM(CAST(goods_amount AS DECIMAL(10,2))) as goods_amount,
                    AVG(CAST(receivable AS DECIMAL(10,2))) as avg_order_amount
                FROM trade_orders 
                WHERE DATE(created) BETWEEN %s AND %s
            """
            
            summary_result = query_db(weekly_summary_sql, [start_date, end_date], True)
            summary_stats = summary_result[0] if summary_result else {}
            
            # 每日销量分布
            daily_distribution_sql = """
                SELECT 
                    DATE(created) as date,
                    COUNT(*) as order_count,
                    SUM(CAST(receivable AS DECIMAL(10,2))) as daily_amount,
                    SUM(CAST(goods_count AS UNSIGNED)) as goods_count
                FROM trade_orders 
                WHERE DATE(created) BETWEEN %s AND %s
                GROUP BY DATE(created)
                ORDER BY date
            """
            
            daily_result = query_db(daily_distribution_sql, [start_date, end_date], True) or []
            
            # 一周热销商品Top10
            weekly_top_goods_sql = """
                SELECT 
                    tog.goods_id,
                    tog.goods_name,
                    SUM(CAST(tog.num AS UNSIGNED)) as total_sold,
                    SUM(CAST(tog.total_amount AS DECIMAL(10,2))) as total_sales_amount,
                    COUNT(DISTINCT DATE(tog.created)) as sale_days,
                    COALESCE(g.brand_name, '') as brand_name,
                    COALESCE(g.class_name, '') as category_name
                FROM trade_order_goods tog
                LEFT JOIN goods g ON tog.goods_id = g.goods_id
                WHERE DATE(tog.created) BETWEEN %s AND %s
                GROUP BY tog.goods_id, tog.goods_name
                ORDER BY total_sold DESC
                LIMIT 10
            """
            
            weekly_top_result = query_db(weekly_top_goods_sql, [start_date, end_date], True) or []
            
            # 客户购买行为分析
            customer_analysis_sql = """
                SELECT 
                    buyer_nick,
                    COUNT(*) as order_count,
                    SUM(CAST(receivable AS DECIMAL(10,2))) as total_spent,
                    AVG(CAST(receivable AS DECIMAL(10,2))) as avg_order_value,
                    MAX(created) as last_order_date
                FROM trade_orders 
                WHERE DATE(created) BETWEEN %s AND %s
                GROUP BY buyer_nick
                ORDER BY total_spent DESC
                LIMIT 5
            """
            
            customer_result = query_db(customer_analysis_sql, [start_date, end_date], True) or []
            
            return {
                "success": True,
                "message": "获取一周销量统计成功",
                "data": {
                    "period": {
                        "start_date": start_date,
                        "end_date": end_date,
                        "days": 7
                    },
                    "summary": {
                        "order_count": int(summary_stats.get('order_count', 0)),
                        "customer_count": int(summary_stats.get('customer_count', 0)),
                        "total_amount": float(summary_stats.get('total_amount', 0)),
                        "goods_amount": float(summary_stats.get('goods_amount', 0)),
                        "avg_order_amount": float(summary_stats.get('avg_order_amount', 0)),
                        "daily_avg_orders": round(float(summary_stats.get('order_count', 0)) / 7, 2),
                        "daily_avg_amount": round(float(summary_stats.get('total_amount', 0)) / 7, 2)
                    },
                    "daily_distribution": [
                        {
                            "date": str(item.get('date')),
                            "order_count": int(item.get('order_count', 0)),
                            "amount": float(item.get('daily_amount', 0)),
                            "goods_count": int(item.get('goods_count', 0))
                        }
                        for item in daily_result
                    ],
                    "top_goods": [
                        {
                            "goods_id": item.get('goods_id'),
                            "goods_name": item.get('goods_name'),
                            "total_sold": int(item.get('total_sold', 0)),
                            "total_sales_amount": float(item.get('total_sales_amount', 0)),
                            "sale_days": int(item.get('sale_days', 0)),
                            "brand_name": item.get('brand_name', ''),
                            "category_name": item.get('category_name', '')
                        }
                        for item in weekly_top_result
                    ],
                    "top_customers": [
                        {
                            "customer_id": item.get('buyer_nick'),
                            "order_count": int(item.get('order_count', 0)),
                            "total_spent": float(item.get('total_spent', 0)),
                            "avg_order_value": float(item.get('avg_order_value', 0)),
                            "last_order_date": str(item.get('last_order_date', ''))
                        }
                        for item in customer_result
                    ]
                }
            }
            
        except Exception as e:
            return {"success": False, "message": f"获取一周销量统计失败：{str(e)}"}
    
    def get_monthly_sales(self, year_month: Optional[str] = None) -> Dict[str, Any]:
        """获取一个月销量统计"""
        try:
            # 如果没有指定年月，使用当前月
            if not year_month:
                year_month = datetime.now().strftime('%Y-%m')
            
            start_date = f"{year_month}-01"
            # 计算月末日期
            next_month = datetime.strptime(start_date, '%Y-%m-%d') + timedelta(days=32)
            end_date = (next_month.replace(day=1) - timedelta(days=1)).strftime('%Y-%m-%d')
            
            # 月度总体统计
            monthly_summary_sql = """
                SELECT 
                    COUNT(*) as order_count,
                    COUNT(DISTINCT buyer_nick) as customer_count,
                    SUM(CAST(receivable AS DECIMAL(10,2))) as total_amount,
                    SUM(CAST(goods_amount AS DECIMAL(10,2))) as goods_amount,
                    SUM(CAST(post_amount AS DECIMAL(10,2))) as shipping_amount,
                    AVG(CAST(receivable AS DECIMAL(10,2))) as avg_order_amount,
                    MIN(created) as first_order,
                    MAX(created) as last_order
                FROM trade_orders 
                WHERE DATE(created) BETWEEN %s AND %s
            """
            
            summary_result = query_db(monthly_summary_sql, [start_date, end_date], True)
            summary_stats = summary_result[0] if summary_result else {}
            
            # 每日销量趋势
            daily_trend_sql = """
                SELECT 
                    DATE(created) as date,
                    COUNT(*) as order_count,
                    SUM(CAST(receivable AS DECIMAL(10,2))) as daily_amount,
                    COUNT(DISTINCT buyer_nick) as customer_count
                FROM trade_orders 
                WHERE DATE(created) BETWEEN %s AND %s
                GROUP BY DATE(created)
                ORDER BY date
            """
            
            trend_result = query_db(daily_trend_sql, [start_date, end_date], True) or []
            
            # 月度商品销量排行Top20
            monthly_goods_ranking_sql = """
                SELECT 
                    tog.goods_id,
                    tog.goods_name,
                    SUM(CAST(tog.num AS UNSIGNED)) as total_sold,
                    SUM(CAST(tog.total_amount AS DECIMAL(10,2))) as total_sales_amount,
                    COUNT(*) as order_count,
                    COUNT(DISTINCT DATE(tog.created)) as sale_days,
                    AVG(CAST(tog.price AS DECIMAL(10,2))) as avg_price,
                    COALESCE(g.brand_name, '') as brand_name,
                    COALESCE(g.class_name, '') as category_name,
                    COALESCE(g.goods_unit, '') as goods_unit
                FROM trade_order_goods tog
                LEFT JOIN goods g ON tog.goods_id = g.goods_id
                WHERE DATE(tog.created) BETWEEN %s AND %s
                GROUP BY tog.goods_id, tog.goods_name
                ORDER BY total_sold DESC
                LIMIT 20
            """
            
            ranking_result = query_db(monthly_goods_ranking_sql, [start_date, end_date], True) or []
            
            # 品类销量分析
            category_analysis_sql = """
                SELECT 
                    COALESCE(g.class_name, '未分类') as category_name,
                    COUNT(*) as item_count,
                    SUM(CAST(tog.num AS UNSIGNED)) as total_quantity,
                    SUM(CAST(tog.total_amount AS DECIMAL(10,2))) as total_amount,
                    COUNT(DISTINCT tog.goods_id) as unique_goods
                FROM trade_order_goods tog
                LEFT JOIN goods g ON tog.goods_id = g.goods_id
                WHERE DATE(tog.created) BETWEEN %s AND %s
                GROUP BY g.class_name
                ORDER BY total_amount DESC
            """
            
            category_result = query_db(category_analysis_sql, [start_date, end_date], True) or []
            
            # 计算日期范围内的天数
            date_start = datetime.strptime(start_date, '%Y-%m-%d')
            date_end = datetime.strptime(end_date, '%Y-%m-%d')
            total_days = (date_end - date_start).days + 1
            
            return {
                "success": True,
                "message": "获取月度销量统计成功",
                "data": {
                    "period": {
                        "year_month": year_month,
                        "start_date": start_date,
                        "end_date": end_date,
                        "total_days": total_days
                    },
                    "summary": {
                        "order_count": int(summary_stats.get('order_count', 0)),
                        "customer_count": int(summary_stats.get('customer_count', 0)),
                        "total_amount": float(summary_stats.get('total_amount', 0)),
                        "goods_amount": float(summary_stats.get('goods_amount', 0)),
                        "shipping_amount": float(summary_stats.get('shipping_amount', 0)),
                        "avg_order_amount": float(summary_stats.get('avg_order_amount', 0)),
                        "daily_avg_orders": round(float(summary_stats.get('order_count', 0)) / total_days, 2),
                        "daily_avg_amount": round(float(summary_stats.get('total_amount', 0)) / total_days, 2),
                        "first_order": str(summary_stats.get('first_order', '')),
                        "last_order": str(summary_stats.get('last_order', ''))
                    },
                    "daily_trend": [
                        {
                            "date": str(item.get('date')),
                            "order_count": int(item.get('order_count', 0)),
                            "amount": float(item.get('daily_amount', 0)),
                            "customer_count": int(item.get('customer_count', 0))
                        }
                        for item in trend_result
                    ],
                    "goods_ranking": [
                        {
                            "rank": idx + 1,
                            "goods_id": item.get('goods_id'),
                            "goods_name": item.get('goods_name'),
                            "total_sold": int(item.get('total_sold', 0)),
                            "total_sales_amount": float(item.get('total_sales_amount', 0)),
                            "order_count": int(item.get('order_count', 0)),
                            "sale_days": int(item.get('sale_days', 0)),
                            "avg_price": float(item.get('avg_price', 0)),
                            "brand_name": item.get('brand_name', ''),
                            "category_name": item.get('category_name', ''),
                            "goods_unit": item.get('goods_unit', '')
                        }
                        for idx, item in enumerate(ranking_result)
                    ],
                    "category_analysis": [
                        {
                            "category_name": item.get('category_name'),
                            "item_count": int(item.get('item_count', 0)),
                            "total_quantity": int(item.get('total_quantity', 0)),
                            "total_amount": float(item.get('total_amount', 0)),
                            "unique_goods": int(item.get('unique_goods', 0)),
                            "avg_amount_per_item": round(float(item.get('total_amount', 0)) / max(int(item.get('item_count', 1)), 1), 2)
                        }
                        for item in category_result
                    ]
                }
            }
            
        except Exception as e:
            return {"success": False, "message": f"获取月度销量统计失败：{str(e)}"}
    
    def get_top_products(self, period: str = 'month', limit: int = 10) -> Dict[str, Any]:
        """获取热销商品排行"""
        try:
            results = self.sales_repository.get_top_products(period, limit)
            
            # 确保结果是列表
            if not isinstance(results, list):
                logger.error(f"热销商品数据格式错误: {type(results)}")
                return {
                    'code': 400,
                    'message': '获取热销商品排行失败：数据格式错误',
                    'data': []
                }
            
            return {
                'code': 200,
                'message': '获取热销商品排行成功',
                'data': results
            }
            
        except Exception as e:
            logger.error(f"获取热销商品排行失败: {str(e)}")
            return {
                'code': 400,
                'message': f'获取热销商品排行失败：{str(e)}',
                'data': []
            }
    
    def get_sales_curve(self, period: str = 'month', granularity: str = 'day') -> Dict[str, Any]:
        """获取销售曲线数据"""
        try:
            results = self.sales_repository.get_sales_curve(period, granularity)
            
            # 确保结果是列表
            if not isinstance(results, list):
                logger.error(f"销售曲线数据格式错误: {type(results)}")
                return {
                    'code': 400,
                    'message': '获取销售曲线数据失败：数据格式错误',
                    'data': []
                }
            
            return {
                'code': 200,
                'message': '获取销售曲线数据成功',
                'data': results
            }
            
        except Exception as e:
            logger.error(f"获取销售曲线数据失败: {str(e)}")
            return {
                'code': 400,
                'message': f'获取销售曲线数据失败：{str(e)}',
                'data': []
            }
    
    def get_comprehensive_statistics(self) -> Dict[str, Any]:
        """获取综合统计数据"""
        try:
            # 调用其他统计方法获取综合数据
            daily_stats = self.get_daily_sales()
            weekly_stats = self.get_weekly_sales()
            monthly_stats = self.get_monthly_sales()
            top_products = self.get_top_products(period='month', limit=10)
            sales_curve = self.get_sales_curve(period='month', granularity='day')
            
            return {
                "success": True,
                "message": "获取综合统计数据成功",
                "data": {
                    "generated_at": datetime.now().isoformat(),
                    "daily": daily_stats.get('data') if daily_stats.get('success') else None,
                    "weekly": weekly_stats.get('data') if weekly_stats.get('success') else None,
                    "monthly": monthly_stats.get('data') if monthly_stats.get('success') else None,
                    "top_products": top_products.get('data') if top_products.get('success') else None,
                    "sales_curve": sales_curve.get('data') if sales_curve.get('success') else None
                }
            }
            
        except Exception as e:
            return {"success": False, "message": f"获取综合统计数据失败：{str(e)}"}

    def get_goods_sales_by_category(self, start_date: str, end_date: str):
        """按分类统计商品销售 - 修复字段名"""
        query = """
        SELECT 
            COALESCE(g.class_name, '未分类') as category_name,  # 修复：category_no -> class_name
            COUNT(*) as item_count,
            SUM(CAST(tog.num AS UNSIGNED)) as total_quantity,
            SUM(CAST(tog.total_amount AS DECIMAL(10,2))) as total_amount,
            COUNT(DISTINCT tog.goods_id) as unique_goods
        FROM trade_order_goods tog
        LEFT JOIN goods g ON tog.goods_id = g.goods_id
        WHERE DATE(tog.created) BETWEEN %s AND %s
        GROUP BY g.class_name  # 修复：category_no -> class_name
        ORDER BY total_amount DESC
        """
        return self._execute_query(query, (start_date, end_date)) 